package DBLayer;
import ModelLayer.*;

import java.sql.*;
import java.util.ArrayList;

public class DBSupplier implements IFDBSupplier  {

	private Connection con;
	public DBSupplier()
	{
		con= DbConnection1.getInstance().getDBcon();
		
	}
	
	public ArrayList<Supplier> getAllSuppliers(boolean retrieveAssociation)
	{
		return miscWhere("",retrieveAssociation);
	}
	
	public Supplier findSupplier(String cvrNo, boolean retrieveAssociation)
	{
		String wClause= " cvr=   '" + cvrNo +"'";
		return singleWhere(wClause, retrieveAssociation);
	}
	

	public int insertSupplier(Supplier supplier) throws Exception
	{
		int rc=-1;
		String query = " Insert into Supplier (cvr, name,address,country, phoneno,email ) VALUES('"+
				supplier.getCvrNo() + "', '" +
				supplier.getName() + "', '" +
				supplier.getAddress() + "', '" +
				supplier.getCountry() + "', '" +
				supplier.getPhoneNo() + "', '" +
				supplier.getEmail() + "')";
System.out.println("insert : " + query);
		
		try{Statement stmt = con.createStatement();
				stmt.setQueryTimeout(5);
				rc = stmt.executeUpdate(query);
				stmt.close();
				}catch(SQLException ex){
					System.out.println("Supplier does not exist");
					throw new Exception ("insertion was not correct");
				}
		
		return rc;
	    
		
	}
	
	public int updateSupplier(Supplier supplier)
	{
		Supplier supObj  = supplier;
		int rc=-1;
	  
		String query="UPDATE Supplier SET "+
		 	  "name ='"+ supObj.getName() +"', "+
		 	  "address ='"+ supObj.getAddress() + "', "+
		 	  "country ='" + supObj.getCountry()+ "', "+
		 	  "phoneno ='" + supObj.getPhoneNo() + "', "+
		 	  "email ='" + supObj.getEmail() + "'"+
		          " WHERE cvr = '"+ supObj.getCvrNo() + "'";
                System.out.println("Update query:" + query);
  		try{ // update employee
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);
                      
	 	 	stmt.close();
		}//end try	
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in supplier db: "+ex);
	  	}
		return(rc);
	}
	
	
	private ArrayList<Supplier> miscWhere(String wClause, boolean retrieveAssociation)
	{
            ResultSet results;
	    ArrayList<Supplier> list = new ArrayList<Supplier>();	
		
	    String query =  buildQuery(wClause);
  
            try{ // read the employee from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
	
		while( results.next() ){
	     	 Supplier supplier = new Supplier();
		 supplier= buildSupplier(results);	
                 list.add(supplier);	
		}//end while
                 stmt.close();     
                
			
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	
	
	
	
	public Supplier singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Supplier supplier= new Supplier();
	
		 String query =  buildQuery(wClause);
         System.out.println(query);
         try{ // read the employee from the database
        	 Statement stmt = con.createStatement();
        	 stmt.setQueryTimeout(5);
        	 results = stmt.executeQuery(query);


        	 if( results.next() ){
                     supplier = buildSupplier(results);
                     //association is to be build
                    
                     stmt.close();
                     
}
                 else{ //no customer was found
                     supplier = null;
                 }
}//end try 
catch(Exception e){
System.out.println("Query exception: "+e);
}
return supplier;
}
		
	
	
	
	private String buildQuery(String wClause)
	{
		String query="Select CVR,name, address, country, phoneNo, email from Supplier ";
		if(wClause.length()>0)
			query=query+"WHERE"+wClause;
		return query;
	}//end buildQuery
	
	private Supplier buildSupplier(ResultSet results)
	{
		Supplier supplier= new Supplier();
		try{
		    supplier.setCvrNo(results.getString("CVR"));
			supplier.setName(results.getString("Name"));
			supplier.setAddress(results.getString("Address"));
			supplier.setCountry(results.getString("Country"));
			supplier.setPhoneNo(results.getString("PhoneNo"));
			supplier.setEmail(results.getString("Email"));
			
			
			
		}//end try
		catch(Exception e)
		{
			System.out.println("error in building the supplier object");
		}//end catch
		
		return supplier;
		
	}//end buildSupplier

	@Override
	public int delete(Supplier sup) {
		int rc = -1;

		String query = "DELETE FROM Supplier WHERE CVR= '" + sup.getCvrNo()
				+ "'";
		System.out.println(query);
		try { // delete from OrderLine
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}// end try
		catch (Exception ex) {
			System.out.println("Delete exception in supplier db: " + ex);
		}
		return (rc);
	}
	
}
